package edu.gatech.cc.hwa.server.database;

import static edu.gatech.cc.hwa.server.database.Statements.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedList;

import edu.gatech.cc.hwa.server.HistoryEntry;
import edu.gatech.cc.hwa.server.Profile;
import edu.gatech.cc.hwa.server.User;
import edu.gatech.cc.hwa.server.database.exceptions.DatabaseException;
import edu.gatech.cc.hwa.server.database.exceptions.NoSuchUserException;
import edu.gatech.cc.hwa.server.database.exceptions.UserAlreadyExistsException;

public class Database {
	
	private static final String PWD = "";
	private static final String URL = "jdbc:mysql://localhost";
	private static final String USER = "root";
	
	//load drivers
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public static void createUser(String username, String hashedPassword) throws DatabaseException {
		try {
			Connection con = createConnection();
			try {
				fetchUser(con, username);
				throw new UserAlreadyExistsException(username);
			} catch (NoSuchUserException e) {/*Normal behavior*/}
			insertUser(con, username, hashedPassword);
			User user = fetchUser(con, username);
			createProfile(con, user);
			con.close();
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}
	}
	
	public static Collection<Profile> getProfilesForUser(User user) throws DatabaseException {
		Collection<Profile> profiles = new ArrayList<Profile>();
		try {
			Connection con = createConnection();
			profiles.addAll(fetchProfilesForUser(con, user));
			con.close();
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}
		return profiles;
	}
	
	public static User getUser(String username) throws DatabaseException {
		try {
			Connection con = createConnection();
			User user = fetchUser(con, username);
			con.close();
			return user;
		} catch (SQLException e) {
			throw new DatabaseException(e);
		} 
	}
	
	public static void updateProfile(Profile profile) throws DatabaseException { 
		try {
			Connection con = createConnection();
			updateProfile(con, profile);
			updateHistoryEntries(con, profile);
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}
	}
	
	private static Connection createConnection() throws SQLException {
		return DriverManager.getConnection(URL, USER, PWD);
	}

	private static void createHistoryEntry(Connection con, Integer profileId, HistoryEntry entry) throws SQLException {
		PreparedStatement stmt = con.prepareStatement(INSERT_HISTORY_ENTRY);
		stmt.setInt(1, profileId);
		stmt.setInt(2, entry.getSequenceNumber());
		stmt.setInt(3, entry.getBrowser());//browser
		stmt.setInt(4, entry.getDayOfWeek());//day of week
		stmt.setString(5, entry.getIpAddress());//ipaddress
		stmt.setInt(6, entry.getOperatingSystem());//operating system
		stmt.setInt(7, entry.getTimeToTypePassword());//timetotypepassword
		stmt.setInt(8, entry.getTimeOfDay());//time of day
		stmt.execute();
		stmt.close();
	}
	
	private static void createProfile(Connection con, User user) throws DatabaseException {
		try {
			insertProfile(con, user.getUserId());
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}
	}
	
	private static Collection<HistoryEntry> fetchHistoryEntries(Connection con, Integer profileId) throws SQLException {
		Collection<HistoryEntry> entries = new LinkedList<HistoryEntry>();
		PreparedStatement stmt = con.prepareStatement(SELECT_HISTORY_ENTRIES);
		stmt.setInt(1, profileId);
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			entries.add(readHistotyEntry(rs, profileId));
		}
		rs.close();
		stmt.close();
		return entries;
	}

	private static Collection<Profile> fetchProfilesForUser(Connection con, User user) throws SQLException {
		Collection<Profile> profiles = new LinkedList<Profile>();
		PreparedStatement stmt = con.prepareStatement(SELECT_USER_PROFILES);
		stmt.setInt(1, user.getUserId());
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			profiles.add(readProfile(rs));
		}
		rs.close();
		stmt.close();
		addHistoryEntries(con, profiles);
		return profiles;
	}
	
	private static void addHistoryEntries(Connection con, Collection<Profile> profiles) throws SQLException {
		for (Profile profile : profiles) {
			for (HistoryEntry entry : fetchHistoryEntries(con, profile.getProfileId())) {
				profile.addHistoryEntry(entry);
			}
		}
	}

	private static User fetchUser(Connection con, String username) throws NoSuchUserException, SQLException {
		PreparedStatement stmt = con.prepareStatement(SELECT_USER_BY_NAME);
		stmt.setString(1, username);
		ResultSet rs = stmt.executeQuery();
		if (rs.next()) {
			Integer userId = rs.getInt(1);
			String passwordHash = rs.getString(2);
			rs.close();
			stmt.close();
			return new User(userId, username, passwordHash);
		} else {
			throw new NoSuchUserException(username);
		}
	}
	
	private static void insertProfile(Connection con, Integer userId) throws SQLException {
		PreparedStatement stmt = con.prepareStatement(INSERT_PROFILE);
		stmt.setInt(1, userId);
		stmt.execute();
		stmt.close();
	}

	private static void insertUser(Connection con, String username, String hashedPassword) throws SQLException {
		PreparedStatement stmt = con.prepareStatement(INSERT_USER);
		stmt.setString(1, username);
		stmt.setString(2, hashedPassword);
		stmt.executeUpdate();
		stmt.close();
	}
	
	private static HistoryEntry readHistotyEntry(ResultSet rs, Integer profileId) throws SQLException {
		Integer entryId = rs.getInt("Entry_ID");
		HistoryEntry entry = new HistoryEntry(profileId, entryId);
		entry.setBrowser(rs.getInt("Browser"));
		entry.setDayOfWeek(rs.getInt("DayOfWeek"));
		entry.setIpAddress(rs.getString("IpAddress"));
		entry.setOperatingSystem(rs.getInt("OperatingSystem"));
		entry.setSequenceNumber(rs.getInt("SequenceNumber"));
		entry.setTimeOfDay(rs.getInt("TimeOfDay"));
		entry.setTimeToTypePassword(rs.getInt("TimeToTypePassword"));
		return entry;
	}
	
	private static Profile readProfile(ResultSet rs) throws SQLException {
		Integer userId = rs.getInt("UID");
		Integer profileId = rs.getInt("PID");
		Profile profile = new Profile(userId, profileId);
		profile.setBroswerVendor(rs.getInt("Broswer"));
		profile.setDayOfWeek(rs.getInt("DayOfWeek"));
		profile.setIpAddress(rs.getString("IpAddress"));
		profile.setOperatingSystem(rs.getInt("OperatingSystem"));
		profile.setTimeOfDay(rs.getInt("TimeOfDay"));
		profile.setTimeToTypePassword(rs.getInt("TimeToTypePassword"));
		return profile;
	}
	
	private static void updateHistoryEntries(Connection con, Profile profile) throws SQLException {
		Iterator<HistoryEntry> iterator = profile.getEntries().iterator();
		HistoryEntry newestEntry = iterator.next();
		while (iterator.hasNext()) {
			HistoryEntry possiblyNewer = iterator.next();
			if (newestEntry.getSequenceNumber() < possiblyNewer.getSequenceNumber()) {
				newestEntry = possiblyNewer;
			}
		}
		if (newestEntry.getEntryID() == -1) {
			createHistoryEntry(con, profile.getProfileId(), newestEntry);
		} else {
			updateHistoryEntry(con, newestEntry);
		}
	}
	
	private static void updateHistoryEntry(Connection con, HistoryEntry entry) throws SQLException {
		PreparedStatement stmt = con.prepareStatement(UPDATE_HISTORY_ENTRY);
		stmt.setInt(1, entry.getSequenceNumber());
		stmt.setInt(2, entry.getBrowser());
		stmt.setInt(3, entry.getDayOfWeek());
		stmt.setString(4, entry.getIpAddress());
		stmt.setInt(5, entry.getOperatingSystem());
		stmt.setInt(6, entry.getTimeToTypePassword());
		stmt.setInt(7, entry.getTimeOfDay());
		stmt.setInt(8, entry.getEntryID());
		stmt.execute();
		stmt.close();
	}

	private static void updateProfile(Connection con, Profile profile) throws SQLException {
		PreparedStatement stmt = con.prepareStatement(UPDATE_PROFILE);
		stmt.setInt(1, profile.getBroswer());
		stmt.setInt(2, profile.getDayOfWeek());
		stmt.setString(3, profile.getIpAddress());
		stmt.setInt(4, profile.getOperatingSystem());
		stmt.setInt(5, profile.getTimeToTypePassword());
		stmt.setInt(6, profile.getTimeOfDay());
		stmt.setInt(7, profile.getProfileId());
		stmt.execute();
		stmt.close();
	}
}
